#! /usr/bin/env python
# coding:utf-8

UI =  '''  0、创建学生数据表
  1、添加学生成绩
  2、修改学生成绩
  3、删除学生成绩
  4、查看成绩
  5、统计成绩
  6、导出成绩表
  7、退出系统'''
GUI ='''-------------------------------------------
|学号     |姓名   |语文     |数学     |英语
-------------------------------------------'''
import  MySQLdb as sql
db = sql.connect('119.29.233.33', 'xuruitao', 'ruitao900825', 'stu')   # 云服务器mysql
#db = sql.connect('127.0.0.1', 'root', 'root', 'stu')
cursor = db.cursor()
setcharset = 'set names utf8'
cursor.execute(setcharset)
db.commit()
def cat_grade():
    print GUI
    mysql = "SELECT id, name, chinese, math, english FROM studata"
    cursor.execute(mysql)
    stu_grade = cursor.fetchall()
    for stu_cat in stu_grade:
        print '| %-8s| %-8s| %-8s| %-8s| %s' \
               % (stu_cat[0], stu_cat[1], stu_cat[2], stu_cat[3], stu_cat[4])
def sort_stunum(stu_grade):
    temp_list = []
    for i in stu_grade:
        temp_list.append(i['学号'])
    temp_list.sort()

def pri_stu(stu_ch, stu_sub, num):
        print '-----------------------'
        print '|学号     |姓名   |', stu_sub
        print '-----------------------'
        for stu in stu_ch:
            print '| %-8s| %-8s| %s' \
                  % (stu[0], stu[1], stu[num+1])
            print '-----------------------'

def sort_sub():
        while True:
            print '''
        1、按语文成绩排名
        2、按数学成绩排名
        3、按英语成绩排名
        4、返回
        '''
            selcet_sub = input('请输入(1-4)：')
            if selcet_sub == 1:
                cursor.execute('SELECT id, name, chinese, math, english from studata ORDER BY  chinese DESC ')
                stu_ch = cursor.fetchall()
                pri_stu(stu_ch, '语文', selcet_sub)
            elif selcet_sub == 2:
                cursor.execute('SELECT id, name, chinese, math, english from studata ORDER BY  math  DESC ')
                stu_ch = cursor.fetchall()
                pri_stu(stu_ch, '数学', selcet_sub)
            elif selcet_sub == 3:
                cursor.execute('SELECT id, name, chinese, math, english from studata ORDER BY  english  DESC ')
                stu_ch = cursor.fetchall()
                pri_stu(stu_ch, '英语', selcet_sub)
            elif selcet_sub == 4:
                break
            else:
                print '输入有误，请重新输入'